{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 将比赛TXT文件转换为CSV文件"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true,
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import gc\n",
    "df=pd.read_csv(r'D:\\Data\\IJCAI\\round1_ijcai_18_test_a_20180301\\round1_ijcai_18_test_a_20180301.txt',sep=' ')\n",
    "df.to_csv(r'D:\\Data\\IJCAI\\test.csv',index=False)\n",
    "del df\n",
    "gc.collect()\n",
    "dff=pd.read_csv(r'D:\\Data\\IJCAI\\round1_ijcai_18_train_20180301\\round1_ijcai_18_train_20180301.txt',sep=' ')\n",
    "dff.to_csv(r'D:\\Data\\IJCAI\\train.csv',index=False)\n",
    "del dff                           \n",
    "gc.collect()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['.ipynb_checkpoints',\n",
       " 'preprocess.ipynb',\n",
       " 'Preprocess_no_fixed-newday.ipynb',\n",
       " 'Preprocess_no_fixed.ipynb',\n",
       " 'result.ipynb',\n",
       " 'result_no-fixed-newday.ipynb',\n",
       " 'result_no_fixed.ipynb',\n",
       " 'untitled.txt',\n",
       " 'Visualization.ipynb']"
      ]
     },
     "execution_count": 1,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import os\n",
    "j=os.listdir(r'C:\\Users\\13307\\Desktop\\新增未备份文件\\IJCAI\\github')\n",
    "lis=[]\n",
    "for i in j:\n",
    "    lis.append(i)\n",
    "lis"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 看一下item_category_list列可以拆成几列，每列需要多少数字，可以看到第一份是相同的，可以忽略"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "\n",
    "j=0\n",
    "listrain=[]\n",
    "listest=[]\n",
    "for data in combine:\n",
    "    data['item_category_list_0']=0\n",
    "    data['item_category_list_1']=0\n",
    "    for ind in data.index:\n",
    "        if j==0:\n",
    "            if len(data.loc[ind,'item_category_list'].split(';'))==3:\n",
    "                listrain.append(data.loc[ind,'item_category_list'].split(';')[2])\n",
    "        else:\n",
    "            if len(data.loc[ind,'item_category_list'].split(';'))==3:\n",
    "                listest.append(data.loc[ind,'item_category_list'].split(';')[2])\n",
    "    j=100\n",
    "#set(listrain)\n",
    "#set(listest)\n",
    "h_=train['item_category_list'].apply(lambda x: x.split(';')[1])\n",
    "h=test['item_category_list'].apply(lambda x: x.split(';')[1])\n",
    "# dic={'1968056100269760729','2011981573061447208','22731265849056483','2436715285093487584','2642175453151805566','3203673979138763595','4879721024980945592', '509660095530134768','5755694407684602296','5799347067982556520','7258015885215914736','8277336076276184272','8710739180200009128'}\n",
    "# h=test['item_category_list'].apply(lambda x: x.split(';')[1])\n",
    "# dic={}\n",
    "# count=0\n",
    "# for i in set(h):\n",
    "#     dic[i]=count\n",
    "#     count+=1\n",
    "# dict={'6233669177166538628':0, '8868887661186419229':1}"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 查看每列缺失值的数量和比率函数"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "def missing(combine):\n",
    "    mark=0\n",
    "    for data in combine:\n",
    "        dic={}\n",
    "        dic['Alength']=len(data)\n",
    "        for i in data.isnull().any()[data.isnull().any()].index:\n",
    "            dic[i]=[str(len(data[data[i].isnull()]))+'   '+str(round(100*len(data[data[i].isnull()])/len(data),3))+'%']\n",
    "        if mark==100:\n",
    "            nantest=pd.DataFrame(dic)\n",
    "            break\n",
    "        else:\n",
    "            nantrain=pd.DataFrame(dic)\n",
    "        mark=100\n",
    "    return nantrain,nantest"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true,
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "train=pd.read_csv(r'D:\\Data\\IJCAI\\train.csv',na_values=-1)#,na_values=-1 [160955, 205287, 214183, 314706]\n",
    "test=pd.read_csv(r'D:\\Data\\IJCAI\\test.csv',na_values=-1)\n",
    "combine=[train,test]\n",
    "nantrain,nantest=missing(combine)\n",
    "nantrain #df[df['user_gender_id'].isnull()]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "nantest"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 分拆item_category_list列,最多三个，第一个相同不用管，先增加一个，然后开始分列"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true,
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "j=0\n",
    "for data in combine:\n",
    "    for ind in data.index:\n",
    "        if len(data.loc[ind,'item_category_list'].split(';'))==2:\n",
    "            data.loc[ind,'item_category_list']=data.loc[ind,'item_category_list']+';1000'\n",
    "        if (ind%10000==0) and (ind!=0):\n",
    "            print(ind,end=' ')\n",
    "    #进行分列赋值操作\n",
    "    data['item_category_list_dup'], data['item_category_list_0'],data['item_category_list_1'] = zip(*data['item_category_list'].map(lambda x: x.split(';')))\n",
    "train.to_csv(r'D:\\Data\\IJCAI\\train_pre1.csv',index=False)\n",
    "test.to_csv(r'D:\\Data\\IJCAI\\test_pre1.csv',index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### predict_category_property列里面的属性过多，只考虑类目不考虑属性了，不然行数会增加非常多，而且只使用属性存在的类目！=-1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true,
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "train=pd.read_csv(r'D:\\Data\\IJCAI\\train_pre1.csv',na_values=-1)\n",
    "test=pd.read_csv(r'D:\\Data\\IJCAI\\test_pre1.csv',na_values=-1)\n",
    "train.drop(list(train[train['predict_category_property'].isnull()].index),axis=0,inplace=True)\n",
    "h_=train['predict_category_property'].apply(lambda x: x.split(';'))\n",
    "h=test['predict_category_property'].apply(lambda x: x.split(';'))\n",
    "listrain=[]\n",
    "for i in h_:\n",
    "    for j in i:\n",
    "        if j.split(':')[1]!='-1':\n",
    "            listrain.append(j.split(':')[0])\n",
    "listest=[]\n",
    "for i in h:\n",
    "    for j in i:\n",
    "        if j.split(':')[1]!='-1':\n",
    "            listest.append(j.split(':')[0])\n",
    "combine=[train,test]\n",
    "######################################################################\n",
    "for data in combine:\n",
    "    for i in set(listrain)|set(listest):\n",
    "        data['predict_category_property_%s'%str(i)]=0\n",
    "combine=[train,test]\n",
    "######################################################################\n",
    "for data in combine:\n",
    "    for ind in data.index:\n",
    "        for j in data.loc[ind,'predict_category_property'].split(';'):\n",
    "            if j.split(':')[1]!='-1':\n",
    "                data.loc[ind,'predict_category_property_%s'%str(j.split(':')[0])]=1\n",
    "        if (ind%10000==0) and (ind!=0):\n",
    "            print(ind,end=' ')\n",
    "print('Done!')\n",
    "train.to_csv(r'D:\\Data\\IJCAI\\train_pret.csv',index=False)\n",
    "test.to_csv(r'D:\\Data\\IJCAI\\test_pret.csv',index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 对缺失值进行填充"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "train=pd.read_csv(r'D:\\Data\\IJCAI\\train_pret.csv',na_values=-1)\n",
    "test=pd.read_csv(r'D:\\Data\\IJCAI\\test_pret.csv',na_values=-1)\n",
    "# corr=train.corr()\n",
    "corr=pd.read_csv(r'D:\\Data\\IJCAI\\corr.csv',index_col=0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "print(list(corr[abs(corr['is_trade'])>0.001]['is_trade'].sort_values()[0:27].index))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "combine=[train,test]\n",
    "nantrain,nantest=missing(combine)\n",
    "nantrain #df[df['user_gender_id'].isnull()]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "nantest"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "corr[corr['shop_review_positive_rate']>0.1]['shop_review_positive_rate'].sort_values(ascending=False).head(6)#shop_score_description\n",
    "for ind in train[train['shop_review_positive_rate'].isnull()].index:\n",
    "    item_price_level=train.loc[ind,'item_price_level']\n",
    "    train.loc[ind,'shop_review_positive_rate']=train[train['item_price_level']==item_price_level]['shop_review_positive_rate'].median()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true,
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "corr[corr['shop_score_delivery']>0.1]['shop_score_delivery'].sort_values(ascending=False).head(6)\n",
    "#['shop_score_delivery','shop_score_description','shop_score_service']这四列是同时缺失的需要一起填充\n",
    "#train[train['user_age_level'].isnull()][['is_trade','user_age_level','user_occupation_id','user_star_level']]\n",
    "h=['shop_score_delivery','shop_score_description','shop_score_service']\n",
    "combine=[train,test]\n",
    "for data in combine:\n",
    "    for hj in h:\n",
    "        for ind in data[data[hj].isnull()].index:\n",
    "            shop_review_positive_rate=data.loc[ind,'shop_review_positive_rate']\n",
    "            item_price_level=data.loc[ind,'item_price_level']\n",
    "            temp=data[(data['shop_review_positive_rate']==shop_review_positive_rate)&(data['item_price_level']==item_price_level)]\n",
    "            if temp['shop_score_service'].isnull().any():\n",
    "                data.loc[ind,'shop_score_service']=data[data['item_price_level']==item_price_level]['shop_score_service'].median()\n",
    "            else:\n",
    "                data.loc[ind,'shop_score_service']=temp['shop_score_service'].median()\n",
    "            if temp['shop_score_delivery'].isnull().any():\n",
    "                data.loc[ind,'shop_score_delivery']=data[data['item_price_level']==item_price_level]['shop_score_delivery'].median()\n",
    "            else:\n",
    "                data.loc[ind,'shop_score_delivery']=round(temp[~temp['shop_score_delivery'].isnull()]['shop_score_delivery'].median(),6)\n",
    "            if temp['shop_score_description'].isnull().any():\n",
    "                data.loc[ind,'shop_score_description']=data[data['item_price_level']==item_price_level]['shop_score_description'].median()\n",
    "            else:\n",
    "                data.loc[ind,'shop_score_description']=temp[~temp['shop_score_description'].isnull()]['shop_score_description'].median()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "corr[corr['user_age_level']>0.1]['user_age_level'].sort_values(ascending=False).head(6)\n",
    "#['is_trade','user_age_level','user_occupation_id','user_star_level']这三列是同时缺失的需要一起填充\n",
    "combine=[train,test]\n",
    "h=['user_age_level','user_occupation_id','user_star_level']\n",
    "for data in combine:\n",
    "    for hj in h:\n",
    "        for ind in data[data[hj].isnull()].index:\n",
    "            shop_score_description=data.loc[ind,'shop_score_description']\n",
    "            item_price_level=data.loc[ind,'item_price_level']\n",
    "            temp=data[(data['shop_score_description']==shop_score_description)&(data['item_price_level']==item_price_level)]\n",
    "            if temp['user_age_level'].isnull().any():\n",
    "                data.loc[ind,'user_age_level']=data[data['item_price_level']==item_price_level]['user_age_level'].median()\n",
    "            else:\n",
    "                data.loc[ind,'user_age_level']=temp['user_age_level'].median()\n",
    "            if temp['user_occupation_id'].isnull().any():\n",
    "                if (data[data['shop_score_description']==shop_score_description]['user_occupation_id'].isnull().any())\\\n",
    "                and (data[data['item_price_level']==item_price_level]['user_occupation_id'].isnull().any()):\n",
    "                    data.loc[ind,'user_occupation_id']=data.loc[ind+6,'user_occupation_id']\n",
    "                elif (data[data['shop_score_description']==shop_score_description]['user_occupation_id'].isnull().any())\\\n",
    "                and (not data[data['item_price_level']==item_price_level]['user_occupation_id'].isnull().any()):\n",
    "                    data.loc[ind,'user_occupation_id']=data[data['item_price_level']==item_price_level]['user_occupation_id'].value_counts().index[0]\n",
    "                else:\n",
    "                    data.loc[ind,'user_occupation_id']=data[data['shop_score_description']==shop_score_description]['user_occupation_id'].value_counts().index[0]\n",
    "            else:\n",
    "                data.loc[ind,'user_occupation_id']=temp['user_occupation_id'].value_counts().index[0]\n",
    "            if temp['user_star_level'].isnull().any():\n",
    "                data.loc[ind,'user_star_level']=data[data['shop_score_description']==shop_score_description]['user_star_level'].median()\n",
    "            else:\n",
    "                data.loc[ind,'user_star_level']=temp['user_star_level'].median()\n",
    "    for ind in data[data['user_star_level'].isnull()].index:\n",
    "        data.loc[ind,'user_star_level']=data.loc[ind-6,'user_star_level']\n",
    "    for ind in data[data['user_age_level'].isnull()].index:\n",
    "        data.loc[ind,'user_age_level']=data.loc[ind-6,'user_age_level']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true,
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "corr[corr['item_brand_id']>0.06]['item_brand_id'].sort_values(ascending=False).head(6)\n",
    "combine=[train,test]\n",
    "for data in combine:\n",
    "    for ind in data[data['item_brand_id'].isnull()].index:\n",
    "        shop_id=data.loc[ind,'shop_id']\n",
    "        item_category_list_0=data.loc[ind,'item_category_list_0']\n",
    "        temp=data[(data['shop_id']==shop_id)&(data['item_category_list_0']==item_category_list_0)]\n",
    "        if len(temp['item_brand_id'].isnull())==len(temp):\n",
    "            data.loc[ind,'item_brand_id']=data.loc[ind-1,'item_brand_id']\n",
    "        else:\n",
    "            data.loc[ind,'item_brand_id']=temp['item_brand_id'].value_counts().index[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "corr[corr['item_city_id']>0.006]['item_city_id'].sort_values(ascending=False).head(6)\n",
    "combine=[train,test]\n",
    "for data in combine:\n",
    "    for ind in data[data['item_city_id'].isnull()].index:\n",
    "        item_price_level=data.loc[ind,'item_price_level']\n",
    "        predict_category_property_5755694407684602296=data.loc[ind,'predict_category_property_5755694407684602296']\n",
    "        temp=data[(data['item_price_level']==item_price_level)&(data['predict_category_property_5755694407684602296']==predict_category_property_5755694407684602296)]\n",
    "        data.loc[ind,'item_city_id']=temp['item_city_id'].value_counts().index[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true,
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "# corr[corr['item_sales_level']>0.1]['item_sales_level'].sort_values(ascending=False).head(6)\n",
    "combine=[train,test]\n",
    "for data in combine:\n",
    "    for ind in data[data['item_sales_level'].isnull()].index:\n",
    "        item_collected_level=data.loc[ind,'item_collected_level']\n",
    "        item_pv_level=data.loc[ind,'item_pv_level']\n",
    "        temp=data[(data['item_collected_level']==item_collected_level)&(data['item_pv_level']==item_pv_level)]\n",
    "        if temp['item_sales_level'].isnull().any():\n",
    "            data.loc[ind,'item_sales_level']=data[data['item_collected_level']==item_collected_level]['item_sales_level'].median()\n",
    "        else:\n",
    "            data.loc[ind,'item_sales_level']=temp['item_sales_level'].median()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true,
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "corr[corr['user_gender_id']>0.01]['user_gender_id'].sort_values(ascending=False).head(6)\n",
    "combine=[train,test]\n",
    "for data in combine:\n",
    "    count=0\n",
    "    for ind in data[data['user_gender_id'].isnull()].index:\n",
    "        count+=1\n",
    "        user_age_level=data.loc[ind,'user_age_level']\n",
    "        predict_category_property_6007511169620956394=data.loc[ind,'predict_category_property_6007511169620956394']\n",
    "        temp=data[(data['user_age_level']==user_age_level)&(data['predict_category_property_6007511169620956394']==predict_category_property_6007511169620956394)]\n",
    "        if (len(temp)==0) and (len(data[data['user_age_level']==user_age_level]['user_gender_id'])!=0):\n",
    "            data.loc[ind,'user_gender_id']=data[data['user_age_level']==user_age_level]['user_gender_id'].value_counts().index[0]\n",
    "        elif (len(temp)==0) and (len(data[data['user_age_level']==user_age_level]['user_gender_id'])==0):\n",
    "            data.loc[ind,'user_gender_id']=data[data['predict_category_property_6007511169620956394']==predict_category_property_6007511169620956394]['user_gender_id'].value_counts().index[0]\n",
    "        elif temp['user_gender_id'].isnull().any():\n",
    "            data.loc[ind,'user_gender_id']=data['user_gender_id'].value_counts().index[0]\n",
    "        else:\n",
    "            data.loc[ind,'user_gender_id']=temp['user_gender_id'].value_counts().index[0]\n",
    "        if count%1000==0:\n",
    "            print(count,end=' ')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 提取前一天、前两天的转换率"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true,
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "train=pd.read_csv(r'D:\\Data\\IJCAI\\train_mispret.csv')#,na_values=-1 [160955, 205287, 214183, 314706]\n",
    "test=pd.read_csv(r'D:\\Data\\IJCAI\\test_mispret.csv')\n",
    "combine=[test]\n",
    "train['timestamp']=pd.to_datetime(train['context_timestamp'].copy(),unit='s')\n",
    "train['day']=train['timestamp'].dt.day\n",
    "train['num_conversion']=0\n",
    "train['num_conversiontd']=0\n",
    "for day in set(train['day'].values):\n",
    "    if day<19:\n",
    "        continue\n",
    "    else:\n",
    "        for u in set(train[train['day']==day]['item_id'].values):\n",
    "            if u in train[train['day']==(day-1)]['item_id'].values:\n",
    "                train.loc[train[(train['item_id']==u)&(train['day']==day)].index,'num_conversion']=\\\n",
    "                len(train[(train['item_id']==u)&(train['day']==(day-1))&(train['is_trade']==1)])/len(train[(train['item_id']==u)&(train['day']==(day-1))])\n",
    "            if u in train[(train['day']==(day-1))|(train['day']==(day-2))]['item_id'].values:\n",
    "                train.loc[train[(train['item_id']==u)&(train['day']==day)].index,'num_conversiontd']=\\\n",
    "                (len(train[(train['item_id']==u)&(train['day']==(day-1))&(train['is_trade']==1)])+\\\n",
    "                 len(train[(train['item_id']==u)&(train['day']==(day-2))&(train['is_trade']==1)]))/(len(train[(train['item_id']==u)&(train['day']==(day-1))])+\\\n",
    "                                                                                                    len(train[(train['item_id']==u)&(train['day']==(day-2))]))\n",
    "combine=[test]\n",
    "for data in combine:\n",
    "    data['num_conversion']=0\n",
    "    for u in set(data['item_id'].values):\n",
    "        if u in train[train['day']==24]['item_id'].values:\n",
    "            data.loc[data[data['item_id']==u].index,'num_conversion']=\\\n",
    "            len(train[(train['item_id']==u)&(train['day']==24)&(train['is_trade']==1)])/len(train[(train['item_id']==u)&(train['day']==24)])\n",
    "        if u in train[(train['day']==24)|(train['day']==23)]['item_id'].values:\n",
    "            data.loc[data[data['item_id']==u].index,'num_conversiontd']=\\\n",
    "            (len(train[(train['item_id']==u)&(train['day']==24)&(train['is_trade']==1)])+\\\n",
    "             len(train[(train['item_id']==u)&(train['day']==23)&(train['is_trade']==1)]))/(len(train[(train['item_id']==u)&(train['day']==24)])+\\\n",
    "                                                                                                len(train[(train['item_id']==u)&(train['day']==23)]))\n",
    "\n",
    "train.drop(['timestamp','day'],axis=1,inplace=True)\n",
    "train.to_csv(r'D:\\Data\\IJCAI\\train_mispret.csv',index=False)\n",
    "test.to_csv(r'D:\\Data\\IJCAI\\test_mispret.csv',index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 提取每天用户点击数，商品被点击数，各个时间间隔,并将train和test重新分开，test按照最开始的顺序排序"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "train=pd.read_csv(r'D:\\Data\\IJCAI\\train_mispret.csv')#,na_values=-1 [160955, 205287, 214183, 314706]\n",
    "test=pd.read_csv(r'D:\\Data\\IJCAI\\test_mispret.csv')\n",
    "train['timestamp']=pd.to_datetime(train['context_timestamp'].copy(),unit='s')\n",
    "test['timestamp']=pd.to_datetime(test['context_timestamp'].copy(),unit='s')\n",
    "train['day']=train['timestamp'].dt.day\n",
    "test['day']=test['timestamp'].dt.day\n",
    "train['num_click']=0\n",
    "test['num_click']=0\n",
    "train['num_show']=0\n",
    "test['num_show']=0\n",
    "\n",
    "train=train.sort_values(by=['day','user_id'])                               #同一天同一个用户的点击广告的次数——--用户的搜索次数和点击次数\n",
    "h=train.groupby(by=['day','user_id']).count()\n",
    "lis=[]\n",
    "for i,j in h.index:\n",
    "    lis.extend([h.loc[i].loc[j]['user_gender_id']]*h.loc[i].loc[j]['user_gender_id'])\n",
    "train['num_click']=lis\n",
    "\n",
    "train=train.sort_values(by=['day','item_id'])                              #同一天同一件商品被搜索点击次数-----商品的被搜索次数和受欢迎程度\n",
    "h=train.groupby(by=['day','item_id']).count()\n",
    "lis=[]\n",
    "for i,j in h.index:\n",
    "    lis.extend([h.loc[i].loc[j]['user_gender_id']]*h.loc[i].loc[j]['user_gender_id'])\n",
    "train['num_show']=lis\n",
    "    \n",
    "train=train.sort_values(by=['item_id','context_timestamp'])                 #同一件商品被搜索点击的时间间隔\n",
    "train.index=np.arange(len(train))\n",
    "lis=[]\n",
    "for val in set(train['item_id'].values):\n",
    "    lis.append(train[train['item_id']==val].index[-1])\n",
    "temp=train['context_timestamp'].diff()\n",
    "for i in lis:\n",
    "    temp.loc[i+1]=0\n",
    "temp.loc[0]=0\n",
    "train['item_time_diff']=temp\n",
    "\n",
    "train=train.sort_values(by=['day','item_id','context_timestamp'])          #同一天同一件商品的被搜索点击的时间间隔\n",
    "train.index=np.arange(len(train))\n",
    "lis=[]\n",
    "for d in [17,18,19,20,21,22,23,24]:\n",
    "    for val in set(train[train['day']==d]['item_id'].values):\n",
    "        lis.append(train[(train['item_id']==val)&(train['day']==d)].index[-1])\n",
    "temp=train['context_timestamp'].diff()\n",
    "for i in lis:\n",
    "    temp.loc[i+1]=0\n",
    "temp.loc[0]=0\n",
    "train['day_item_diff']=temp\n",
    "    \n",
    "train=train.sort_values(by=['day','user_id','context_timestamp'])          #同一天相同用户的不同商品点击时间间隔\n",
    "train.index=np.arange(len(train))\n",
    "lis=[]\n",
    "for d in [17,18,19,20,21,22,23,24]:\n",
    "    for val in set(train[train['day']==d]['user_id'].values):\n",
    "        lis.append(train[(train['user_id']==val)&(train['day']==d)].index[-1])\n",
    "temp=train['context_timestamp'].diff()\n",
    "for i in lis:\n",
    "    temp.loc[i+1]=0\n",
    "temp.loc[0]=0\n",
    "train['day_user_diff']=temp\n",
    "train.drop(['timestamp','day'],axis=1,inplace=True)\n",
    "train.to_csv(r'D:\\Data\\IJCAI\\train_mispret.csv',index=False)\n",
    "print('Train has being Done!')\n",
    "##########################################################################################################################################\n",
    "test=test.sort_values(by=['day','user_id'])                               #同一天同一个用户的点击广告的次数——--用户的搜索次数和点击次数\n",
    "h=test.groupby(by=['day','user_id']).count()\n",
    "lis=[]\n",
    "for i,j in h.index:\n",
    "    lis.extend([h.loc[i].loc[j]['user_gender_id']]*h.loc[i].loc[j]['user_gender_id'])\n",
    "test['num_click']=lis\n",
    "\n",
    "test=test.sort_values(by=['day','item_id'])                              #同一天同一件商品被搜索点击次数-----商品的被搜索次数和受欢迎程度\n",
    "h=test.groupby(by=['day','item_id']).count()\n",
    "lis=[]\n",
    "for i,j in h.index:\n",
    "    lis.extend([h.loc[i].loc[j]['user_gender_id']]*h.loc[i].loc[j]['user_gender_id'])\n",
    "test['num_show']=lis\n",
    "    \n",
    "test=test.sort_values(by=['item_id','context_timestamp'])                 #同一件商品被搜索点击的时间间隔\n",
    "test.index=np.arange(len(test))\n",
    "lis=[]\n",
    "for val in set(test['item_id'].values):\n",
    "    lis.append(test[test['item_id']==val].index[-1])\n",
    "temp=test['context_timestamp'].diff()\n",
    "for i in lis:\n",
    "    temp.loc[i+1]=0\n",
    "temp.loc[0]=0\n",
    "test['item_time_diff']=temp\n",
    "\n",
    "test=test.sort_values(by=['day','item_id','context_timestamp'])          #同一天同一件商品的被搜索点击的时间间隔\n",
    "test.index=np.arange(len(test))\n",
    "lis=[]\n",
    "for d in [24,25]:\n",
    "    for val in set(test[test['day']==d]['item_id'].values):\n",
    "        lis.append(test[(test['item_id']==val)&(test['day']==d)].index[-1])\n",
    "temp=test['context_timestamp'].diff()\n",
    "for i in lis:\n",
    "    temp.loc[i+1]=0\n",
    "temp.loc[0]=0\n",
    "test['day_item_diff']=temp\n",
    "    \n",
    "test=test.sort_values(by=['day','user_id','context_timestamp'])          #同一天相同用户的不同商品点击时间间隔\n",
    "test.index=np.arange(len(test))\n",
    "lis=[]\n",
    "for d in [24,25]:\n",
    "    for val in set(test[test['day']==d]['user_id'].values):\n",
    "        lis.append(test[(test['user_id']==val)&(test['day']==d)].index[-1])\n",
    "temp=test['context_timestamp'].diff()\n",
    "for i in lis:\n",
    "    temp.loc[i+1]=0\n",
    "temp.loc[0]=0\n",
    "test['day_user_diff']=temp\n",
    "\n",
    "test.drop(['timestamp','day'],axis=1,inplace=True)\n",
    "test.to_csv(r'D:\\Data\\IJCAI\\test_mispret.csv',index=False)\n",
    "\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "test=pd.read_csv(r'D:\\Data\\IJCAI\\test_mispret.csv')\n",
    "new=pd.read_csv(r'D:\\Data\\IJCAI\\test.csv')\n",
    "lis=[]\n",
    "for i in new.index:\n",
    "    lis.extend(list(test[test['instance_id']==new.loc[i,'instance_id']].index))\n",
    "test=test.loc[lis]\n",
    "test.index=np.arange(len(test))\n",
    "test.to_csv(r'D:\\Data\\IJCAI\\test_mispret.csv',index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import math\n",
    "tr=pd.read_csv(r'D:\\Data\\IJCAI\\train_pret.csv',usecols=['instance_id','item_property_list','item_category_list','predict_category_property'])\n",
    "tr.drop_duplicates(inplace=True)\n",
    "train=pd.read_csv(r'D:\\Data\\IJCAI\\train_mispret.csv')#,na_values=-1 [160955, 205287, 214183, 314706]\n",
    "test=pd.read_csv(r'D:\\Data\\IJCAI\\test_mispret.csv')\n",
    "te=pd.read_csv(r'D:\\Data\\IJCAI\\test_pret.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "collapsed": true,
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "test=pd.concat([test,te[['item_property_list','item_category_list','predict_category_property']]],axis=1)\n",
    "train=train.sort_values(by=['instance_id'])\n",
    "tr=tr.sort_values(by=['instance_id'])\n",
    "tr.index=np.arange(len(tr))\n",
    "train.index=np.arange(len(train))\n",
    "train=pd.concat([train,tr[['item_property_list','item_category_list','predict_category_property']]],axis=1)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 广告商品和预测属性和类目数量和比率"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "10000 20000 30000 40000 50000 60000 70000 80000 90000 100000 110000 120000 130000 140000 150000 160000 170000 180000 190000 200000 210000 220000 230000 240000 250000 260000 270000 280000 290000 300000 310000 320000 330000 340000 350000 360000 370000 380000 390000 400000 410000 420000 430000 440000 450000 460000 470000 10000 47824.31797790527\n"
     ]
    }
   ],
   "source": [
    "import time \n",
    "s=time.time()\n",
    "combine=[train,test]\n",
    "for data in combine:\n",
    "    data['com_pre_cate']=0\n",
    "    data['com_pre_pro']=0\n",
    "    data['com_pre_cateprob']=0\n",
    "    data['com_pre_proprob']=0\n",
    "    data['num_true_category']=0\n",
    "    data['num_true_property']=0\n",
    "    data['num_pred_category']=0\n",
    "    data['num_pred_property']=0\n",
    "    for ind in data.index:\n",
    "        Category=data.loc[ind,'item_category_list'].split(';')\n",
    "        Property=data.loc[ind,'item_property_list'].split(';')\n",
    "        liscate=[]\n",
    "        lispro=[]\n",
    "        for i in data.loc[ind,'predict_category_property'].split(';'):\n",
    "            liscate.append(i.split(':')[0])\n",
    "            lispro.extend(i.split(':')[1].split(','))\n",
    "        while '1000' in Category:\n",
    "            Category.remove('1000')\n",
    "        while '-1' in lispro:\n",
    "            lispro.remove('-1')\n",
    "        comcate=len(set(Category)&set(liscate))\n",
    "        compro=len(set(Property)&set(lispro))\n",
    "        data.loc[ind,['com_pre_cate','com_pre_pro','com_pre_cateprob','com_pre_proprob','num_true_category','num_true_property',\\\n",
    "                      'num_pred_category','num_pred_property']]=[comcate,compro,comcate/math.sqrt(len(Category)*len(liscate)+1),\\\n",
    "                                                                 compro/math.sqrt(len(Property)*len(lispro)+1),len(Category),len(Property),\\\n",
    "                                                                 len(liscate),len(lispro)]\n",
    "        if (ind %10000==0) and (ind!=0):\n",
    "            print(ind,end=' ')\n",
    "e=time.time()\n",
    "print(e-s)\n",
    "train.to_csv(r'D:\\Data\\IJCAI\\train_mispret.csv',index=False)\n",
    "test.to_csv(r'D:\\Data\\IJCAI\\test_mispret.csv',index=False)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
